﻿using Kingdee.BOS;
using Kingdee.BOS.Contracts.Report;
using Kingdee.BOS.Core.Report;
using Kingdee.BOS.Orm.DataEntity;
using Kingdee.BOS.ServiceHelper;
using Kingdee.BOS.Util;
using System.Collections.Generic;
using System.ComponentModel;

namespace Caraya.MFG.BusinessPlugIn
{
    [Description("二开简单账表-采购物料计算汇总"), HotUpdate]
    public class PurTotalPlugIn : SysReportBaseService
    {
        public override void Initialize()
        {
            base.Initialize();
            this.ReportProperty.ReportType = ReportType.REPORTTYPE_NORMAL;
            this.ReportProperty.ReportName = new LocaleValue("采购物料计算汇总", base.Context.UserLocale.LCID);
            this.ReportProperty.PrimaryKeyFieldName = "FMATERIALID";
            this.IsCreateTempTableByPlugin = true;

        }
         

        public override ReportTitles GetReportTitles(IRptParams filter)
        {
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            var mList = new List<string>();
            if (customFilter["F_CARA_MulBase"] is DynamicObjectCollection materials)
            {
                foreach (var m in materials)
                {
                    var n = m["F_CARA_MulBase"] as DynamicObject;
                    var n1 = n["Number"];
                    mList.Add(n1.ToString());
                }
            }
            var whereFempId = string.Join(",", mList);
            ReportTitles reportTitles = new ReportTitles();
            if (customFilter != null)
            {
                reportTitles.AddTitle("F_CARA_Text", whereFempId);
            }
            return reportTitles;
        }

        public override void BuilderReportSqlAndTempTable(IRptParams filter, string tableName)
        {
            base.BuilderReportSqlAndTempTable(filter, tableName);
            DynamicObject customFilter = filter.FilterParameter.CustomFilter;
            var mList = new List<string>();
            if (customFilter["F_CARA_MulBase"] is DynamicObjectCollection materials)
            {
                foreach (var m in materials)
                {
                    var n = m["F_CARA_MulBase"] as DynamicObject;
                    var n1 = n["Id"];
                    mList.Add(n1.ToString());
                }
            }
            var whereFempId = string.Join(",", mList);
            string text = @"/*dialect*/
SELECT t1.*
	INTO {0} 
FROM ( 
SELECT ROW_NUMBER() over(Order by FNumber ASC) As FIDENTITYID, FMATERIALID, FNumber, FSpecification, FName, SUM(FINVENTORYQUANTITY) AS Fkcsl, SUM(FUNCLAIMEDNUMBER) AS Fscwl, 
                SUM(FREMAINSTOCKINQTY) AS Fcgwr, SUM(FINVENTORYQUANTITY) + SUM(FUNCLAIMEDNUMBER) 
                + SUM(FREMAINSTOCKINQTY) AS Fjcsl
FROM      (SELECT   e1.FMATERIALID, e1.FNumber, e1.FSpecification, e1.FName, SUM(e0.FQty) AS FINVENTORYQUANTITY, 
                                 0 AS FUNCLAIMEDNUMBER, 0 AS FREMAINSTOCKINQTY
                 FROM      (SELECT   FSTOCKORGID AS FStockOrgId, FBASEQTY AS FQty, FSTOCKSTATUSID AS FStockStatusId, 
                                                  FMATERIALID AS FMaterialId
                                  FROM      T_STK_INVENTORY AS t0
                                  WHERE   (FOBJECTTYPEID = 'STK_Inventory')) AS e0 INNER JOIN
                                     (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                                      t0_L.FNAME AS FName
                                      FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                                      T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND 
                                                      t0_L.FLOCALEID = 2052) AS e1 ON e0.FMaterialId = e1.FMATERIALID
                 WHERE   (e0.FStockStatusId = 10000) AND (e0.FStockOrgId = 1) AND (e0.FMaterialId IN ({1}))
                 GROUP BY e1.FMATERIALID, e1.FNumber, e1.FSpecification, e1.FName
                 UNION ALL
                 SELECT   e2.FMATERIALID, e2.FNumber, e2.FSpecification, e2.FName, 0 AS FINVENTORYQUANTITY, - SUM(e1_2.FNoPickedQty) 
                                 AS FUNCLAIMEDNUMBER, 0 AS FREMAINSTOCKINQTY
                 FROM      (SELECT   FID, FPRDORGID AS FPrdOrgId, FMOENTRYID AS FMOEntryID
                                  FROM      T_PRD_PPBOM AS t0
                                  WHERE   (FFORMID = 'PRD_PPBOM')) AS e0_2 INNER JOIN
                                     (SELECT   t0.FID, t1_Q.FNOPICKEDQTY AS FNoPickedQty, t1.FMATERIALID AS FMaterialID2
                                      FROM      T_PRD_PPBOM AS t0 LEFT OUTER JOIN
                                                      T_PRD_PPBOMENTRY AS t1 ON t0.FID = t1.FID LEFT OUTER JOIN
                                                      T_PRD_PPBOMENTRY_Q AS t1_Q ON t1.FENTRYID = t1_Q.FENTRYID
                                      WHERE   (t0.FFORMID = 'PRD_PPBOM')) AS e1_2 ON e0_2.FID = e1_2.FID INNER JOIN
                                     (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                                      t0_L.FNAME AS FName
                                      FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                                      T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND 
                                                      t0_L.FLOCALEID = 2052) AS e2 ON e1_2.FMaterialID2 = e2.FMATERIALID INNER JOIN
                                     (SELECT   t1.FENTRYID AS FEntryId, t1_A.FSTATUS AS FStatus
                                      FROM      T_PRD_MO AS t0 LEFT OUTER JOIN
                                                      T_PRD_MOENTRY AS t1 ON t0.FID = t1.FID LEFT OUTER JOIN
                                                      T_PRD_MOENTRY_A AS t1_A ON t1.FENTRYID = t1_A.FENTRYID
                                      WHERE   (t0.FFORMID = 'PRD_MO')) AS e3 ON e0_2.FMOEntryID = e3.FEntryId
                 WHERE   (e1_2.FMaterialID2 IN ({1})) AND (e3.FStatus <> N'6') AND (e3.FStatus <> N'7')
                 GROUP BY e2.FMATERIALID, e2.FNumber, e2.FSpecification, e2.FName
                 UNION ALL
                 SELECT   e2_1.FMATERIALID, e2_1.FNumber, e2_1.FSpecification, e2_1.FName, 0 AS FINVENTORYQUANTITY, 0 AS FUNCLAIMEDNUMBER, 
                                 SUM(e1_1.FRemainStockINQty) AS FREMAINSTOCKINQTY
                 FROM      (SELECT   FID, FPURCHASEORGID AS FPurchaseOrgId, FDOCUMENTSTATUS AS FDocumentStatus, 
                                                  FCLOSESTATUS AS FCloseStatus
                                  FROM      T_PUR_POORDER AS t0
                                  WHERE   (FOBJECTTYPEID = 'PUR_PurchaseOrder')) AS e0_1 INNER JOIN
                                     (SELECT   t0.FID, t3_R.FREMAINSTOCKINQTY AS FRemainStockINQty, t3.FMATERIALID AS FMaterialId, 
                                                      t3.FMRPFREEZESTATUS AS FMRPFreezeStatus, t3.FMRPCLOSESTATUS AS FMRPCloseStatus, 
                                                      t3.FMRPTERMINATESTATUS AS FMRPTerminateStatus
                                      FROM      T_PUR_POORDER AS t0 LEFT OUTER JOIN
                                                      T_PUR_POORDERENTRY AS t3 ON t0.FID = t3.FID LEFT OUTER JOIN
                                                      T_PUR_POORDERENTRY_R AS t3_R ON t3.FENTRYID = t3_R.FENTRYID
                                      WHERE   (t0.FOBJECTTYPEID = 'PUR_PurchaseOrder')) AS e1_1 ON e0_1.FID = e1_1.FID INNER JOIN
                                     (SELECT   t0.FMATERIALID, t0.FNUMBER AS FNumber, t0_L.FSPECIFICATION AS FSpecification, 
                                                      t0_L.FNAME AS FName
                                      FROM      T_BD_MATERIAL AS t0 LEFT OUTER JOIN
                                                      T_BD_MATERIAL_L AS t0_L ON t0.FMATERIALID = t0_L.FMATERIALID AND 
                                                      t0_L.FLOCALEID = 2052) AS e2_1 ON e1_1.FMaterialId = e2_1.FMATERIALID
                 WHERE   (e0_1.FDocumentStatus = N'C') AND (e0_1.FCloseStatus = N'A') AND (e1_1.FMaterialId IN ({1})) 
                                 AND (e1_1.FMRPCloseStatus = N'A') AND (e1_1.FMRPFreezeStatus = N'A') AND 
                                 (e1_1.FMRPTerminateStatus = N'A')
                 GROUP BY e2_1.FMATERIALID, e2_1.FNumber, e2_1.FSpecification, e2_1.FName) AS TT
GROUP BY FMATERIALID, FNumber, FSpecification, FName
) t1
";
            var sql = string.Format(text, tableName, whereFempId);
            DBServiceHelper.ExecuteDynamicObject(this.Context, sql);
        }

        public override ReportHeader GetReportHeaders(IRptParams filter)
        {
            ReportHeader header = new ReportHeader();
            var localEid = this.Context.UserLocale.LCID;
            header.AddChild("FNumber", new LocaleValue("物料编码", localEid));
            header.AddChild("FName", new LocaleValue("物料名称", localEid));
            header.AddChild("FSpecification", new LocaleValue("型号规格", localEid));
            header.AddChild("Fkcsl", new LocaleValue("库存数量", localEid));
            header.AddChild("Fscwl", new LocaleValue("生产用料未领", localEid));
            header.AddChild("Fcgwr", new LocaleValue("采购订单未入", localEid));
            header.AddChild("Fjcsl", new LocaleValue("计算结存", localEid));
            return header;
        }

        public override void CloseReport()
        {
            this.DropTempTable();
        }
    }


}
